package org.jbit.news.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.jbit.news.dao.BaseDao;
import org.jbit.news.dao.NewsDao;
import org.jbit.news.entity.News;

public class NewsDaoImpl extends BaseDao implements NewsDao {
	public List<News> getAllnews() {
		List<News> list = new ArrayList<News>();
		String sql = "select * from news,topic where news.ntid = topic.tid order by ntid";
		try{
			exceuteQuery(sql);
			while(rs.next()){
				News news = new News();
				news.setNid(rs.getInt("nid"));
				news.setNtid(rs.getInt("ntid"));
				news.setNtitle((String)rs.getObject("ntitle"));
				news.setNauthor((String)rs.getObject("nauthor"));
				news.setNcontent((String)rs.getObject("ncontent"));
				news.setNtname(String.valueOf(rs.getObject("tname")));
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				news.setNcreatedate(sdf.format(rs.getTimestamp("ncreatedate")));
				list.add(news);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(conn,pstmt,rs);
		}	
		return list;
	}
	@Override
	public int save(News news) {
		String sql="insert into news (nid,ntid,ntitle,nauthor,ncontent,nsummary) values (?,?,?,?,?,?)";
		Object[] param={1001,news.getNtid(),news.getNtitle(),news.getNauthor(),news.getNcontent(),news.getNsummary()};
		int result=this.exceuteUpdate(sql, param);
		return result;
	}
	@Override
	public int del(int nid) {
		String sql="delete from news where nid=?";
		int result=exceuteUpdate(sql,nid);
		return result;
	}
	@Override
	public int update(News news,int id) {
		int result=0;
		String sql="update news set ntid=?,ntitle=?,nauthor=?,nsummary=?,ncontent=? where nid=?";
		Object[] param={news.getNtid(),news.getNtitle(),news.getNauthor(),news.getNsummary(),news.getNcontent(),id};
		result=this.exceuteUpdate(sql, param);
		return result;
	}
	@Override
	public News getByName(String name, String password) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public List<News> findByName(String name) {
		List<News> newsList = new ArrayList<News>();
		String sql="select * from news where ntitle like '%"+name+"%'";
		rs=exceuteQuery(sql);
		try{
			while (rs.next()) {
				News news = new News();
				news.setNid(rs.getInt("nid"));
				news.setNtid(rs.getInt("ntid"));
				news.setNtitle((String) rs.getObject("ntitle"));
				news.setNauthor((String) rs.getObject("nauthor"));
				news.setNcontent((String) rs.getObject("ncontent"));
				news.setNtname(String.valueOf(rs.getObject("ntitle")));
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				news.setNcreatedate(sdf.format(rs.getTimestamp("ncreatedate")));
				newsList.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, rs);
		}
		return newsList;
	}
	@Override
	public List<News> findByType(String type) {
		// TODO Auto-generated method stub
		return null;
	}
	public ResultSet findNtid(){
		ResultSet rs=null;
		String sql="select topic.tname from news,topic where news.ntid=topic.tid";
		rs=exceuteQuery(sql);
		return rs;
	}
	@Override
	public int getNewsCount() {
		int count=-1;
		String sql="select count(1) from news,topic where news.ntid=topic.tid";
		exceuteQuery(sql);
		try {
			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeAll(conn, pstmt, rs);
		}
		return count;
	}
	@Override
	public List<News> getNewsByPage(int pageIndex,int pageSize) {
		List<News> newsList = new ArrayList<News>();
		String sql="select * from news,topic where news.ntid=topic.tid order by ncreateDate desc limit ?,?";
		int i = (pageIndex-1)*pageSize;
		exceuteQuery(sql,i,pageSize);
	try{
		while (rs.next()) {
			News news = new News();
			news.setNid(rs.getInt("nid"));
			news.setNtid(rs.getInt("ntid"));
			news.setNtitle((String) rs.getObject("ntitle"));
			news.setNauthor((String) rs.getObject("nauthor"));
			news.setNcontent((String) rs.getObject("ncontent"));
			news.setNtname(String.valueOf(rs.getObject("ntitle")));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			news.setNcreatedate(sdf.format(rs.getTimestamp("ncreatedate")));
			newsList.add(news);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		closeAll(conn, pstmt, rs);
	}
		return newsList;
	}
	@Override
	public News getById(int id) {
		News news = null;
		String sql="select * from news where nid=?";
		exceuteQuery(sql,id);
		try {
			while(rs.next()){
				news = new News();
				news.setNid(rs.getInt("nid"));
				news.setNtid(rs.getInt("ntid"));
				news.setNtitle((String)rs.getObject("ntitle"));
				news.setNauthor((String)rs.getObject("nauthor"));
				news.setNcontent((String)rs.getObject("ncontent"));
				news.setNsummary((String)rs.getObject("nsummary"));
				news.setNtname(String.valueOf(rs.getObject("ntitle")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, rs);
		}
		return news;
	}
}
